package personnel.department.pages.finder;


import static personnel.department.dao.HibernateUtils.getDAO;

import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.stream.Collector;
import java.util.stream.Collectors;

import org.eclipse.jface.layout.GridDataFactory;
import org.eclipse.swt.SWT;
import org.eclipse.swt.graphics.Image;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Composite;
import org.eclipse.swt.widgets.Event;
import org.eclipse.swt.widgets.FileDialog;
import org.eclipse.swt.widgets.Listener;
import org.hibernate.Query;
import org.hibernate.Session;

import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;

import personnel.department.dao.HibernateUtils;
import personnel.department.dao.entity.Employee;
import personnel.department.pages.PagesLogic;
import personnel.department.pages.SearchPersonalPage;
import personnel.department.pages.constants.Icons;
import personnel.department.pages.widgets.CheckedWidget;
import personnel.department.pages.widgets.DateWidget;
import personnel.department.utils.DateUtils;
import personnel.department.utils.ExportUtils;

public class FinderPage extends SearchPersonalPage {
	
	private CheckedWidget certificateCheck;
	private DateWidget fromCertificate;
	private DateWidget toCertificate;
	
	private CheckedWidget qualificationCheck;
	private DateWidget fromQualification;
	private DateWidget toQualification;

	private CheckedWidget validationCheck;
	private DateWidget fromValidation;
	private DateWidget toValidation;
	private Button find;

	public FinderPage(String pageName) {
		super(pageName);
	}

	@Override
	protected void createSeachContent(Composite parent) {
//		ScrolledComposite sc = createScrolledComposite(parent);
		Composite c = new Composite(parent, SWT.NONE);
		c.setLayout(new GridLayout(6, false));
		GridDataFactory.fillDefaults().grab(true, false).applyTo(c);
		
		certificateCheck = new CheckedWidget(c, "Последующие сертификаты", null) {
			@Override
			protected void modify() {
				findValidate();
				super.modify();
			}

		};
		fromCertificate = new DateWidget(c, "С", null);
		toCertificate = new DateWidget(c, "До", null);
		
		qualificationCheck = new CheckedWidget(c, "Последующие повышение квалификации", null) {
			@Override
			protected void modify() {
				findValidate();
				super.modify();
			}
		};
		fromQualification = new DateWidget(c, "С", null);
		toQualification = new DateWidget(c, "До", null);
		
		validationCheck = new CheckedWidget(c, "Подтверждение аттестации", null) {
			@Override
			protected void modify() {
				findValidate();
				super.modify();
			}
		};
		fromValidation = new DateWidget(c, "С", null);
		toValidation = new DateWidget(c, "До", null);
//		settingScrolled(sc, c);
	}
	
	@Override
	protected void createButtonComposite(Composite parent) {
		Composite c = new Composite(parent, SWT.NONE);
		c.setLayout(new GridLayout(2, false));
		GridDataFactory.fillDefaults().grab(true, false).applyTo(c);
		find = new Button(c, SWT.PUSH);
		find.setImage(new Image(find.getDisplay(), Icons.SEARCH_ICON.getPath()));
		find.setText("Поиск");
		find.setEnabled(false);
		find.addListener(SWT.Selection, new Listener() {
			
			@Override
			public void handleEvent(Event event) {
				List<Employee> employees = Sets.newHashSet(Iterables.concat(employeeByCertificate(), 
						employeeByQualifications(), 
						employeeByValidations())).stream().collect(Collectors.toList());
				table.clear();
				table.addElements(employees);
				quantityRows.setText("Количество записей: " + String.valueOf(employees.size()));
				quantityRows.pack();
			}

			private List<Employee> employeeByValidations() {
				List<Employee> employees = Lists.newArrayList();
				if (qualificationCheck.getValue()) {
					Date from = fromQualification.getValue();
					Date to = toQualification.getValue();
					
					Session session = HibernateUtils.getSession();
					Query query = session.createQuery("SELECT e.id, max(v.confirmation) "
							+ "FROM Employee e JOIN e.education.validations v "
							+ "GROUP BY e.id");
					
					List<Object[]> result = query.list();
					for (Object[] values : result) {
						Long employeeId = (Long) values[0];
						Date lastConfirmation = (Date) values[1];
						if (DateUtils.between(lastConfirmation, from, to)) {
							Employee employee = (Employee) getDAO().getObjectById(employeeId, Employee.class);
							employees.add(employee);
						}
					}
				}
				return employees;
			}
			
			private List<Employee> employeeByQualifications() {
				List<Employee> employees = Lists.newArrayList();
				if (qualificationCheck.getValue()) {
					Date from = fromQualification.getValue();
					Date to = toQualification.getValue();
					
					Session session = HibernateUtils.getSession();
					Query query = session.createQuery("SELECT e.id, max(q.dateTesting) "
							+ "FROM Employee e JOIN e.education.qualifications q "
							+ "GROUP BY e.id");
					
					List<Object[]> result = query.list();
					for (Object[] values : result) {
						Long employeeId = (Long) values[0];
						Date lastConfirmation = (Date) values[1];
						if (DateUtils.between(lastConfirmation, from, to)) {
							Employee employee = (Employee) getDAO().getObjectById(employeeId, Employee.class);
							employees.add(employee);
						}
					}
				}
				return employees;
			}
			
			private List<Employee> employeeByCertificate() {
				List<Employee> employees = Lists.newArrayList();
				if (certificateCheck.getValue()) {
					Date from = fromCertificate.getValue();
					Date to = toCertificate.getValue();
					
					Session session = HibernateUtils.getSession();
					Query query = session.createQuery("SELECT e.id, max(c.confirmation) "
							+ "FROM Employee e JOIN e.education.certificates c "
							+ "GROUP BY e.id");
					
					List<Object[]> result = query.list();
					for (Object[] values : result) {
						Long employeeId = (Long) values[0];
						Date lastConfirmation = (Date) values[1];
						if (DateUtils.between(lastConfirmation, from, to)) {
							Employee employee = (Employee) getDAO().getObjectById(employeeId, Employee.class);
							employees.add(employee);
						}
					}
				}
				return employees;
			}
		});
		
		Button export = new Button(c, SWT.PUSH);
		export.setImage(new Image(export.getDisplay(), Icons.EXPORT_ICON.getPath()));
		export.setText("Экспорт в CSV");
		export.addListener(SWT.Selection, new Listener() {
			
			@Override
			public void handleEvent(Event event) {
		        FileDialog fd = new FileDialog(PagesLogic.getInstance().getMainShell(), SWT.SAVE);
		        fd.setText("Сохранить");
		        fd.setFilterPath("C:/");
		        String[] filterExt = { "*.csv"};
		        fd.setFilterExtensions(filterExt);
		        String fileName = fd.open();
		        String dataToCsv = table.getDataToCsv();
		        try {
					ExportUtils.toCSV(fileName, dataToCsv);
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		});
	}

	private void findValidate() {
		if (certificateCheck.getValue() || qualificationCheck.getValue() || validationCheck.getValue()) {
			find.setEnabled(true);
		} else {
			find.setEnabled(false);
		}
	}
	
}
